package cn.itcast.jsjwl.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.jsjwl.domain.TeachingNotice;
import cn.itcast.jsjwl.utils.JDBCUtils;

public class TeachingNoticeDao {
	public int findTotalRecords() throws SQLException {
		String sql = "SELECT COUNT(*) FROM teaching_notice";
		   QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		   Long num=(Long)qr.query(sql, new ScalarHandler());
		   return num.intValue();
	}

	public List<TeachingNotice> findTeachingNoticeWithPage(int startIndex, int pageSize) throws SQLException {
		String sql = "SELECT * FROM teaching_notice ORDER BY time DESC LIMIT ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<TeachingNotice>(TeachingNotice.class),startIndex,pageSize);
	}

	public void addTeachingNotice(TeachingNotice teachingNotice) throws SQLException {
		String sql="INSERT INTO teaching_notice (time,address,remark,uname,uptime) VALUES( ? , ? , ? , ? , ? );";
		Object[] params= {teachingNotice.getTime(),teachingNotice.getAddress(),teachingNotice.getRemark(),teachingNotice.getUname(),teachingNotice.getUptime()};
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);
		
	}

	public List<TeachingNotice> findTeachingNoticeWithPageByAdmin(int startIndex, int pageSize) throws SQLException {
		String sql = "SELECT * FROM teaching_notice ORDER BY time DESC LIMIT ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<TeachingNotice>(TeachingNotice.class),startIndex,pageSize);
	}

	public void deleteTeachingNoticeByAdmin(String tnId) throws SQLException {
		String sql="delete FROM teaching_notice WHERE id=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,tnId);
		
	}

	public List<TeachingNotice> findTeachingNoticeByStu(int startIndex, int pageSize) throws SQLException {
		String sql = "SELECT * FROM teaching_notice ORDER BY time DESC LIMIT ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<TeachingNotice>(TeachingNotice.class),startIndex,pageSize);
	}

	public TeachingNotice findTeachingNoticeById(String tId) throws SQLException {
		String sql="SELECT * FROM teaching_notice WHERE id=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<TeachingNotice>(TeachingNotice.class),tId);
	}
}
